from DAL.sqlHelper import  get_keyboard_conn as conn_a

class scriptInfoDAL:
    def getList(self):
        sql ="""
         SELECT * FROM script_use_info
        """
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                # print(res)
                return res
        finally:
            conn.close()

    def add(self,name=''):
        sql = """
               INSERT  INTO script_use_info(`name`,use_count,last_use_time,remark,create_time)
               VALUES('{0}',1,now(),NULL,now())
              """
        sql = sql.format(name)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                return 1
        finally:
            conn.close()

    def update(self,name=''):
        sql = """
               UPDATE script_use_info
                SET use_count=use_count+1,
                    last_use_time=NOW()
                WHERE `name`='{0}'
              """
        sql = sql.format(name)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                return 1
        finally:
            conn.close()

    def ifExist(self,name=''):
        sql = """
                SELECT id FROM script_use_info
                WHERE `name`='{0}'
               """
        sql = sql.format(name)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchone()
                if res:
                    return True
                else:
                    return False
        finally:
            conn.close()

    def addOrUpdate(self,name=''):
        if self.ifExist(name):
            self.update(name)
        else:
            self.add(name)

if __name__ == "__main__":
     # 调试部分---------------------------
     dal = scriptInfoDAL()
     dal.getList()
     # dal.add('xiaoming')
     res = dal.ifExist('xiaoming1')
     dal.addOrUpdate('gly1')
     print(res)